﻿using Anke.SHManage.Model.FixedAssetsModel;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace Anke.SHManage.MSSQLDAL.FixedAssetsDAL
{
    public class FIXEDAssetsDeliveryDAL
    {
        /// <summary>
        /// 获取固定资产出库单列表
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="assetTypeID"></param>
        /// <param name="materialID">资产编码--Guid</param>
        /// <param name="assetID">固定资产编号</param>
        /// <param name="DateStart"></param>
        /// <param name="DateEnd"></param>
        /// <returns></returns>
        public object GetDeliveryList(int pageSize, int pageIndex, string assetTypeID, string materialID, string assetID, DateTime DateStart, DateTime DateEnd)
        {
            List<FIXED_DeliveryInfo> list = new List<FIXED_DeliveryInfo>();
            StringBuilder sb = new StringBuilder();
            sb.Append(" select distinct identity(int,1,1) as 行号 ");
            sb.Append(" ,fd.编码,fd.资产编码,fd.出库时间,fd.出库仓库编码,fd.入库仓库编码,fd.出库人,fd.领用人 ");
            sb.Append(" ,fd.出库存放点,fd.入库存放点,fd.出库车牌号码,fd.入库车牌号码,fd.备注 ");
            sb.Append(" ,资产名称=fm.资产名称,资产编号=fm.固定资产编码,品牌=fm.品牌,型号=fm.型号,规格=fm.规格,主机序列号=fm.主机序列号 ");
            sb.Append(" ,单位=fm.单位,数量=fm.数量,单价=fm.单价,设备管理科室=fm.资产管理部门 ");
            sb.Append(" ,出库仓库=fs1.仓库名称 ");
            sb.Append(" ,入库仓库=fs2.仓库名称 ");
            sb.Append(" into #temp ");
            sb.Append(@"   
                from FIXED_Delivery fd ");
            sb.Append(@"   
                left join FIXED_Matetial fm on fm.编码=fd.资产编码 ");
            sb.Append(@"   
                left join FIXED_Storage fs1 on fs1.仓库编码=fd.出库仓库编码 ");
            sb.Append(@"   
                left join FIXED_Storage fs2 on fs2.仓库编码=fd.入库仓库编码 ");
            sb.Append(@"
                where 1=1 ");
            //分类筛选
            //WhereClauseUtility.AddStringEqual("类型编码", assetTypeID, sb);
            if ((assetTypeID.Length > 0) && (assetTypeID != null) && (assetTypeID != "-1") && (assetTypeID != "--请选择--"))
            {
                List<string> typeIDList = GetTDicStrListByID(assetTypeID); //分类编码list
                //string s1 = string.Join(",", typeIDList.ToArray()); //将list转为string，逗号分隔
                string s1 = "'" + string.Join("','", typeIDList.ToArray()) + "'";
                WhereClauseUtility.AddInSelectQuery("fm.类型编码", s1, sb);
            }
            //资产编码筛选
            if ((materialID != null) && (materialID != "") && (materialID != "-1") && materialID != "--请选择--") {
                Guid MaterialID = new Guid(materialID);
                sb.Append(@" and fd.资产编码='" + MaterialID + "'");            
            }
            WhereClauseUtility.AddStringEqual("fm.固定资产编码", assetID, sb);

            WhereClauseUtility.AddDateTimeGreaterThan("出库时间", DateStart, sb);
            WhereClauseUtility.AddDateTimeLessThan("出库时间", DateEnd, sb);

            sb.Append(@"
                order by 出库时间,资产名称 ");
            sb.Append(@"
                    select top " + pageSize + " A.*  from #temp A where 行号>" + (pageIndex - 1) * pageSize + " order by 行号 ");
            sb.Append(@"
                    SELECT count(*) FROM #temp t
                    drop table #temp ");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        FIXED_DeliveryInfo info = new FIXED_DeliveryInfo();
                        info.BianMa = new Guid(dr["编码"].ToString());
                        info.ZiChanBianMa = new Guid(dr["资产编码"].ToString());
                        info.GuDingZiChanBianHao = dr["资产编号"].ToString();
                        info.ChuKuShiJian = Convert.ToDateTime(dr["出库时间"].ToString());
                        info.ChuKuCangKuBianMa = Convert.ToInt32(dr["出库仓库编码"].ToString());
                        info.ChuKuCangKu = dr["出库仓库"].ToString();
                        info.RuKuCangKuBianMa = Convert.ToInt32(dr["入库仓库编码"].ToString());
                        info.RuKuCangKu = dr["入库仓库"].ToString();
                        info.ChuKuRen = dr["出库人"].ToString(); //可以看为 领用经办人
                        info.LingYongRen = dr["领用人"].ToString();
                        info.ChuKuCunFangDian = dr["出库存放点"].ToString();
                        info.RuKuCunFangDian = dr["入库存放点"].ToString();
                        info.ChuKuChePaiHaoMa = dr["出库车牌号码"].ToString();
                        info.RuKuChePaiHaoMa = dr["入库车牌号码"].ToString();
                        info.BeiZhu = dr["备注"].ToString();
                        info.ZiChanMingCheng = dr["资产名称"].ToString();
                        info.PinPai = dr["品牌"].ToString();
                        info.XingHao = dr["型号"].ToString();
                        info.GuiGe = dr["规格"].ToString();
                        info.ZhuJiXuLieHao = dr["主机序列号"].ToString();
                        info.DanWei = dr["单位"].ToString();
                        info.ShuLiang = dr["数量"].ToString();
                        info.DanJia = Convert.ToDouble(dr["单价"].ToString());
                        info.SheBeiGuanLiKeShi = dr["设备管理科室"].ToString();

                        list.Add(info);
                    }
                }
                int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                var result = new { total = total, rows = list };
                return result;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FIXEDAssetsDeliveryDAL/GetDeliveryList", ex.Message);
                return null;
            }
        }


        /// <summary>
        /// 递归查询该分类ID下的所有list（分类及后代分类）-适用于树结构数据
        /// </summary>
        /// <param name="assetTypeID"></param>
        /// <returns></returns>
        private List<string> GetTDicStrListByID(string assetTypeID)
        {
            List<string> list = new List<string>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"with dic 
                            as( select * 
                                from TDictionary 
                                where ID in ('" + assetTypeID + @"')
                                union all 
                                select TDictionary.* 
                                from dic , TDictionary 
                                where dic.id = TDictionary.ParentID
                            )
                          select * from dic");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        list.Add(dr["ID"].ToString());
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FIXEDAssetsDeliveryDAL/GetTDicStrListByID", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 新增出库单---需要新增出库单表数据及修改基础信息表部分字段
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool InsertEditPageData(FIXED_DeliveryInfo info)
        {
            bool result = false;
            try
            {
                using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
                {
               
                    bool result1 = InsertTableDelivery(info);
                    bool result2 = UpdateTableMatetrial(info);
                   
                    if (result1 && result2)
                    {
                        result = true;
                    }
                    ts.Complete(); //提交事务
                }
                return result;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FIXEDAssetsDeliveryDAL/InsertEditPageData", ex.ToString());
                return false;
            }


        }

        /// <summary>
        /// 更新固定资产基础信息表数据
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        private bool UpdateTableMatetrial(FIXED_DeliveryInfo info)
        {
            bool result = true;
            StringBuilder sb = new StringBuilder();
            sb.Append(" update FIXED_Matetial set ");
            sb.Append(@" 
                        当前仓库编码=" + info.RuKuCangKuBianMa + ", ");
            sb.Append(" 当前存放点='" + info.RuKuCunFangDian + "', ");
            sb.Append(" 当前车牌号码='" + info.RuKuChePaiHaoMa + "' ");
            sb.Append(@" 
                        where 1=1 ");
            sb.Append(@" 
                        and 编码= '" + info.ZiChanBianMa + "'");
            try 
            {
                int intRow = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                return intRow > 0;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FIXEDAssetsDeliveryDAL/UpdateTableMatetrial", ex.Message);
                result = false;
            }
            return result;
        }


        /// <summary>
        /// 向出库单记录表FIXED_Delivery中插入数据
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        private bool InsertTableDelivery(FIXED_DeliveryInfo info)
        {
            bool result = true;
            StringBuilder sb = new StringBuilder();
            info.BianMa = Guid.NewGuid();
            sb.Append(" insert into FIXED_Delivery values (");
            sb.Append(" '" + info.BianMa + "', ");
            sb.Append(" '" + info.ZiChanBianMa + "', ");
            sb.Append(" '" + info.ChuKuShiJian + "', ");
            sb.Append(" " + info.ChuKuCangKuBianMa + ", ");
            sb.Append(" " + info.RuKuCangKuBianMa + ", ");
            sb.Append(" '" + info.ChuKuRen + "', ");
            sb.Append(" '" + info.LingYongRen + "', ");
            sb.Append(" '" + info.ChuKuCunFangDian + "', ");
            sb.Append(" '" + info.RuKuCunFangDian + "', ");
            sb.Append(" '" + info.ChuKuChePaiHaoMa + "', ");
            sb.Append(" '" + info.RuKuChePaiHaoMa + "', ");
            sb.Append(" '" + info.BeiZhu + "' ");
            sb.Append(" )");

            try
            {
                int intRow = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                return intRow > 0;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FIXEDAssetsDeliveryDAL/InsertTableDelivery", ex.Message);
                result = false;
            }
            return result;
        }

        /// <summary>
        /// 根据资产编码获取对应资产基本信息数据
        /// </summary>
        /// <param name="Code"></param>
        /// <returns></returns>
        public FIXED_MatetialInfo GetMaterialData(string Code)
        {
            StringBuilder sb = new StringBuilder();
            Guid Code1 = new Guid(Code);
            sb.Append(@" select fm.* ,当前仓库=fs.仓库名称");
            sb.Append(@" from FIXED_Matetial fm ");
            sb.Append(@" left join FIXED_Storage fs on fs.仓库编码=fm.当前仓库编码 ");
            sb.Append(@" 
                        where 1=1 ");
            sb.Append(" and 编码='" + Code1 + "'");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                DataTable dt = ds.Tables[0];
                var query = from a in dt.AsEnumerable()
                            select new FIXED_MatetialInfo()
                            {
                                BianMa = a.Field<Guid>("编码"),
                                GuDingZiChanBianMa = a.Field<string>("固定资产编码"),
                                LeiXingBianMa = a.Field<string>("类型编码"),
                                ZiChanMingCheng = a.Field<string>("资产名称"),
                                ZhuJiXuLieHao = a.Field<string>("主机序列号"),
                                PinPai = a.Field<string>("品牌"),
                                XingHao = a.Field<string>("型号"),
                                GuiGe = a.Field<string>("规格"),
                                DanWei = a.Field<string>("单位"),
                                ShuLiang = a.Field<string>("数量"),
                                FaPiaoHaoMa = a.Field<string>("发票号码"),
                                DanJia = a.Field<Double>("单价"),
                                GouJianRiQi = a.Field<DateTime>("购建日期"),
                                RuKuRiQi = a.Field<DateTime>("入库日期"),
                                DangQianCangKuBianMa = a.Field<int>("当前仓库编码"),
                                DangQianCangKu = a.Field<string>("当前仓库"),
                                DangQianCunFangDian = a.Field<string>("当前存放点"),
                                DangQianChePaiHaoMa = a.Field<string>("当前车牌号码"),
                                ZiChanGuanLiBuMen = a.Field<string>("资产管理部门"),
                                ZiChanGuanLiRen = a.Field<string>("资产管理人"),
                                BeiZhu = a.Field<string>("备注"),
                                ShiFouBaoFei = a.Field<Boolean>("是否报废"),
                            };
                var result = query.FirstOrDefault();
                return result;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FIXEDAssetsDeliveryDAL/GetMaterialData", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 获取出库单数据
        /// </summary>
        /// <param name="Code"></param>
        /// <returns></returns>
        public FIXED_DeliveryInfo GetDeliveryData(string Code)
        {
            StringBuilder sb = new StringBuilder();
            Guid Code1 = new Guid(Code);
            sb.Append(@" select fd.* ");
            sb.Append(@" from FIXED_Delivery fd ");
            sb.Append(@" 
                        where 1=1 ");
            sb.Append(" and 编码='" + Code1 + "'");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                DataTable dt = ds.Tables[0];
                var query = from a in dt.AsEnumerable()
                            select new FIXED_DeliveryInfo()
                            {
                                BianMa = a.Field<Guid>("编码"),
                                ZiChanBianMa = a.Field<Guid>("资产编码"),
                                ChuKuShiJian = a.Field<DateTime>("出库时间"),
                                ChuKuCangKuBianMa = a.Field<int>("出库仓库编码"),
                                RuKuCangKuBianMa = a.Field<int>("入库仓库编码"),
                                ChuKuRen = a.Field<string>("出库人"),
                                LingYongRen = a.Field<string>("领用人"),
                                ChuKuCunFangDian = a.Field<string>("出库存放点"),
                                RuKuCunFangDian = a.Field<string>("入库存放点"),
                                ChuKuChePaiHaoMa = a.Field<string>("出库车牌号码"),
                                RuKuChePaiHaoMa = a.Field<string>("入库车牌号码"),
                                BeiZhu = a.Field<string>("备注"),
                            };
                var result = query.FirstOrDefault();
                return result;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FIXEDAssetsDeliveryDAL/GetDeliveryData", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 出库单编辑--需要更新出库单表数据及修改基础信息表部分字段
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool UpdateEditPageData(FIXED_DeliveryInfo info)
        {
            bool result = false;
            try
            {
                using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
                {

                    bool result1 = UpdateTableDelivery(info);
                    bool result2 = UpdateTableMatetrial(info);

                    if (result1 && result2)
                    {
                        result = true;
                    }
                    ts.Complete(); //提交事务
                }
                return result;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FIXEDAssetsDeliveryDAL/UpdateEditPageData", ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 更新出库单记录表
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        private bool UpdateTableDelivery(FIXED_DeliveryInfo info)
        {
            bool result = true;
            StringBuilder sb = new StringBuilder();
            sb.Append(" update FIXED_Delivery set ");
            sb.Append(@" 
                        出库时间='" + info.ChuKuShiJian + "', ");
            //add2019-10-08 plq 出库仓库、出库存放点、出库车牌号码改为可编辑
            sb.Append(" 出库仓库编码=" + info.ChuKuCangKuBianMa + ", ");
            sb.Append(" 出库存放点='" + info.ChuKuCunFangDian + "', ");
            sb.Append(" 出库车牌号码='" + info.ChuKuChePaiHaoMa + "', ");
            sb.Append(" 入库仓库编码=" + info.RuKuCangKuBianMa + ", ");
            sb.Append(" 出库人='" + info.ChuKuRen + "', ");
            sb.Append(" 领用人='" + info.LingYongRen + "', ");
            sb.Append(" 入库存放点='" + info.RuKuCunFangDian + "', ");
            sb.Append(" 入库车牌号码='" + info.RuKuChePaiHaoMa + "', ");
            sb.Append(" 备注='" + info.BeiZhu + "' ");
            sb.Append(@" 
                        where 1=1 ");
            sb.Append(@" 
                        and 编码= '" + info.BianMa + "'");
            try
            {
                int intRow = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                return intRow > 0;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FIXEDAssetsDeliveryDAL/UpdateTableDelivery", ex.Message);
                result = false;
            }
            return result;
        }
    }
}
